/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package db.dao;

import core.Course;
import core.Grade;
import core.Group;
import core.Student;
import java.util.List;

/**
 *
 * @author Mikael
 */
public class StudentDAO extends AbstractDAO {

    public static Student read(long primaryKey) {
        final String sql =
                "SELECT * FROM Students WHERE id = ?";
        return jdbc.queryForObject(sql, studentMapper, primaryKey);
    }

    public static List<Student> readAll() {
        final String sql =
                "SELECT * FROM Students";
        return jdbc.query(sql, studentMapper);
    }

    public static List<Student> readN(int startRow, int nRows) {
        final String sql =
                "SELECT * FROM Students LIMIT ?, ?";
        return jdbc.query(sql, studentMapper, startRow, nRows);
    }

    public static int getNRows() {
        final String sql =
                "SELECT COUNT(*) FROM Students";
        return jdbc.queryForInt(sql, DUMMY);
    }

    public static List<Student> search(Student student) {
        final String sql =
                "SELECT * FROM Students WHERE fname LIKE ? AND lname LIKE ? AND address LIKE ? AND email LIKE ?";
        return jdbc.query(sql, studentMapper,
                "%" + student.getFirstName() + "%",
                "%" + student.getLastName() + "%",
                "%" + student.getAddress() + "%",
                "%" + student.getEmail() + "%");
    }

    public static int update(Student student) {
        final String sql =
                "UPDATE Students SET fname=?, lname=?, address=?, email=? WHERE id = ?";
        int nRows = jdbc.update(sql,
                new Object[]{
                    student.getFirstName(),
                    student.getLastName(),
                    student.getAddress(),
                    student.getEmail(),
                    student.getId()
                });
        return nRows;
    }

    public static List<Course> getCourses(long primaryKey) {
        final String sql =
                "SELECT * FROM Courses INNER JOIN StudentCoursePairs ON Courses.id=StudentCoursePairs.courseId AND StudentCoursePairs.studentId = ? ORDER BY Courses.code";
        return jdbc.query(sql, courseMapper, primaryKey);
    }

    public static List<Grade> getGrades(long primaryKey) {
        final String sql =
                "SELECT Courses.id, Courses.code, Courses.name, StudentCoursePairs.grade FROM Courses INNER JOIN StudentCoursePairs ON Courses.id=StudentCoursePairs.courseId AND StudentCoursePairs.studentId = ? ORDER BY Courses.code";
        return jdbc.query(sql, gradeMapper, primaryKey);
    }

    public static List<Course> getCurrentCourses(long primaryKey) {
        final String sql =
                "SELECT * FROM Courses INNER JOIN StudentCoursePairs ON Courses.id=StudentCoursePairs.courseId AND StudentCoursePairs.studentId = ? AND StudentCoursePairs.grade = 0 ORDER BY Courses.code";
        return jdbc.query(sql, courseMapper, primaryKey);
    }

    public static List<Group> getGroups(long primaryKey) {
        final String sql =
                "SELECT Groups.id, Groups.name, Groups.description, Groups.creator, Students.fname, Students.lname FROM Groups INNER JOIN StudentGroupPairs ON Groups.id=StudentGroupPairs.groupId AND StudentGroupPairs.studentId = ? INNER JOIN Students ON Students.id=Groups.creator ORDER BY Groups.name";
        return jdbc.query(sql, groupMapper, primaryKey);
    }

    public static Student searchForPw(String email) {
        final String sql = "SELECT * FROM Students WHERE email = ?";
        Student user;
        try{
            user = jdbc.queryForObject(sql, studentMapper, email);
        }catch(Exception e){
            return null;
        }
        return user;
    }
}
